﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using StockFinder.Model;

namespace StockFinder.DataAccess
{
    public class IndexUrlDataAccess : DataAccessBase
    {
        private const string DOES_INDEX_URL_EXIST_BY_NAME_COMMAND = "dbo.DoesIndexUrlExistByIndexUrl";
        private const string INSERT_INDEX_URL_COMMAND = "InsertIndexUrl";
        private const string GET_ALL_INDEX_URLS = "dbo.GetAllIndexUrls";

        public void InsertIndexUrls(List<IndexUrl> indexUrls)
        {
            foreach (IndexUrl indexUrl in indexUrls)
            {
                InsertIndexUrl(indexUrl);
            }
        }

        public void InsertIndexUrl(IndexUrl indexUrl)
        {
            SqlConnection connection = CreateOpenConnection();
            
            using (SqlCommand command = new SqlCommand(INSERT_INDEX_URL_COMMAND, connection) { CommandType = System.Data.CommandType.StoredProcedure })
            {
                command.Parameters.AddWithValue("@Name", indexUrl.Url);

                command.Parameters.Add(new SqlParameter() { ParameterName = "RETURN_VALUE", Direction = System.Data.ParameterDirection.ReturnValue });

                command.ExecuteNonQuery();

                int result = (int)command.Parameters["RETURN_VALUE"].Value;

                indexUrl.IndexUrlId = result;
            }
        }

        public bool DoesIndexUrlExistByIndexUrl(IndexUrl indexUrl)
        {
            bool exists = false;

            SqlConnection connection = CreateOpenConnection();
            
            using (SqlCommand command = new SqlCommand(DOES_INDEX_URL_EXIST_BY_NAME_COMMAND, connection) { CommandType = System.Data.CommandType.StoredProcedure })
            {
                command.Parameters.AddWithValue("@IndexUrl", indexUrl.Url);                    

                exists = Convert.ToBoolean(command.ExecuteScalar());
            }           

            return exists;
        }

        public List<IndexUrl> GetAllIndexUrls()
        {
            List<IndexUrl> indexUrls = null;

            SqlConnection connection = CreateOpenConnection();
            
            using (SqlCommand command = new SqlCommand(GET_ALL_INDEX_URLS, connection) { CommandType = System.Data.CommandType.StoredProcedure })
            {
                using (SqlDataReader dataReader = command.ExecuteReader())
                {
                    indexUrls = new List<IndexUrl>();

                    if (dataReader.HasRows)
                    {
                        while (dataReader.Read())
                        {
                            int indexUrlId = Convert.ToInt32(dataReader["IndexUrlId"]);
                            string url = Convert.ToString(dataReader["Url"]);
                            string title = Convert.ToString(dataReader["Title"]);

                            indexUrls.Add(new IndexUrl() { IndexUrlId = indexUrlId, Url = url, Title = title });
                        }
                    }
                }
            }           

            return indexUrls;
        }
    }
}
